﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using patents_robot_demo.BasicWork;
namespace patents_robot_demo.DataBase
{
    class DBWork
    {
        static public void SaveLogInformation(string INFO)
        {
            //
        }
        #region 保存专利信息子模块 保存到数据库
        static public void SavePatentInfo(string PatentINFO)
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = "server=.;database=patents;uid=sa;pwd=mingrui";
            SqlCommand mysql = con.CreateCommand();
            string[] INFO = PatentINFO.Split('\n');
            mysql.CommandText = "update Info set " +
                "ApplyDATE='" + INFO[1].Substring(INFO[1].IndexOf("=") + 1, INFO[1].IndexOf("\n")) +
                "',PatentNAME='" + INFO[2].Substring(INFO[2].IndexOf("=") + 1, INFO[2].IndexOf("\n")) +
                "',PublicNUM='" + INFO[3].Substring(INFO[3].IndexOf("=") + 1, INFO[3].IndexOf("\n")) +
                "',PublicDATE='" + INFO[4].Substring(INFO[4].IndexOf("=") + 1, INFO[4].IndexOf("\n")) +
                "',MainSortNUM='" + INFO[5].Substring(INFO[5].IndexOf("=") + 1, INFO[5].IndexOf("\n")) +
                "',SortNUM='" + INFO[6].Substring(INFO[6].IndexOf("=") + 1, INFO[6].IndexOf("\n")) +
                "',ApplyPEOPLE='" + INFO[7].Substring(INFO[7].IndexOf("=") + 1, INFO[7].IndexOf("\n")) +
                "',Address='" + INFO[8].Substring(INFO[8].IndexOf("=") + 1, INFO[8].IndexOf("\n")) +
                "',Inventor='" + INFO[9].Substring(INFO[9].IndexOf("=") + 1, INFO[9].IndexOf("\n")) +
                "',PatentAgencyCOMPANY='" + INFO[10].Substring(INFO[10].IndexOf("=") + 1, INFO[10].IndexOf("\n")) +
                "',Agent='" + INFO[11].Substring(INFO[11].IndexOf("=") + 1, INFO[11].IndexOf("\n")) +
                "',Summary='" + INFO[12].Substring(INFO[12].IndexOf("=") + 1, INFO[12].IndexOf("\n")) +
                "',LawSTATE='" + INFO[13].Substring(INFO[13].IndexOf("=") + 1, INFO[13].IndexOf("\n")) +
                "',DischargeTIME='" + INFO[14].Substring(INFO[14].IndexOf("=") + 1, INFO[14].IndexOf("\n")) +
                "',LogTIME='" + INFO[15].Substring(INFO[15].IndexOf("=") + 1, INFO[15].IndexOf("\n")) +
                "',RecSTATE,'" + "2" +//2代表有专利信息
                "' where PatentID='" + INFO[0].Substring(INFO[0].IndexOf("=") + 1, INFO[0].IndexOf("\n")) + "'";
            try
            {
                con.Open();
                mysql.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                SaveLogInformation(ex.Message);
            }
            finally
            {
                con.Close();
            }

        }
        #endregion
        #region 获得专利号码 存储到数据库
        static public void SavePatentId(string PatentID)
        {
            string ts = null;
            int t1;
            t1 = PatentID.IndexOf("\n");
            SqlConnection con = new SqlConnection();
            con.ConnectionString = "server=.;database=patents;uid=sa;pwd=mingrui";
            SqlCommand mysql = con.CreateCommand();
            ts = PatentID.Substring(0, t1);
            PatentID = PatentID.Substring(t1 + 1);
            mysql.CommandText = "insert into Info(PatentID) values('" + ts + "')";
            try
            {
                con.Open();
                while (t1 != -1)
                {
                    mysql.ExecuteNonQuery();
                    t1 = PatentID.IndexOf("\n");
                }
            }
            catch (Exception ex)
            {
                SaveLogInformation(ex.Message);
            }
            finally
            {
                con.Close();
            }
        }
        #endregion
        #region 插入日志信息
        /// <summary>
        /// 
        /// </summary>
        /// <param name="info"></param>
        /// <param name="kind"></param>
        static public void InsertLogInfo(string info, string kind)
        {
            string t = null;
            t += kind + "','";
            t += info + "','";
            t += EnvironmentInformation.GetSystemTime();
            SqlConnection con = new SqlConnection();
            con.ConnectionString = "server=.;database=patents;uid=sa;pwd=mingrui";

            SqlCommand mysql = con.CreateCommand();
            mysql.CommandText = "insert into LOGINFO(KIND,INFO,TIME) values('" + t + "')";
            try
            {
                con.Open();
                mysql.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                SaveLogInformation(ex.Message);
            }
            finally
            {
                con.Close();
            }
        }
        #endregion
        #region 从数据库中返回所需要的专利号码
        static public string GetPatentID()
        {
            string id = null;
            SqlConnection con = new SqlConnection();
            con.ConnectionString = "server=.;database=patents;uid=sa;pwd=mingrui";
            SqlCommand mysql = con.CreateCommand();
            mysql.CommandText = "select PatentID from Info where RecSTATE is NULL";
            try
            {
                con.Open();

            }
            catch (Exception ex)
            {
                SaveLogInformation(ex.Message);
            }
            SqlDataReader reader = mysql.ExecuteReader();
            if (reader.Read())
            {
                id = reader[0].ToString();
            }
            else
            {
                return null;
            }
            reader.Close();
            SqlCommand mysql1 = con.CreateCommand();
            mysql1.CommandText = "update Info set RecState='1' where PatentID='" + id + "'";//1表示专利号码进入查询队列
            try
            {
                mysql1.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                SaveLogInformation(ex.Message);
            }
            con.Close();
            return id;
        }
        #endregion
        #region 删除指定专利号码的记录
        /// <summary>
        /// 
        /// </summary>
        /// <param name="PatentId"></param>
        static public void DelPatentId(string PatentId)
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = "server=.;database=patents;uid=sa;pwd=mingrui";
            SqlCommand mysqldel = con.CreateCommand();
            mysqldel.CommandText = "delete from Table_2 where PatentID='" + PatentId + "'";
            try
            {
                con.Open();
            }
            catch (Exception ex)
            {
                SaveLogInformation(ex.Message);
            }
            mysqldel.ExecuteNonQuery();
            con.Close();
        }
        #endregion
        #region 获得法律状态为空的专利号码
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        static public string GetRootId()
        {
            string id = null;
            SqlConnection con = new SqlConnection();
            con.ConnectionString = "server=.;database=patents;uid=sa;pwd=mingrui";
            con.Open();
            SqlCommand mysql = con.CreateCommand();
            mysql.CommandText = "select PatentID,LawSTATE=null from Info";
            SqlDataReader reader = mysql.ExecuteReader();
            if (reader.Read())
            {
                id = (string)reader[0];
            }
            con.Close();
            return id;
        }
        #endregion
        #region 更新法律状态
        /// <summary>
        /// 
        /// </summary>
        /// <param name="PatentId"></param>
        /// <param name="LawState"></param>
        static public void UpdateLawState(string PatentId, string LawState)
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = "server=.;database=patents;uid=sa;pwd=mingrui";
            SqlCommand mysql = con.CreateCommand();
            mysql.CommandText = "update Info set LawSTATE='" + LawState + "' where PatentID='" + PatentId + "'";
            try
            {
                con.Open();
                mysql.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                SaveLogInformation(ex.Message);
            }
            finally
            {
                con.Close();
            }
        }
        #endregion
        #region 插入申请人名称及相关查询信息
        /// <summary>
        /// 插入申请人名称及相关查询信息
        /// </summary>
        /// <param name="ApplyPeople">申请人</param>
        /// <param name="Total"></param>
        /// <param name="Finished"></param>
        /// <param name="Speed"></param>
        static public void InsertApplyPeopleInfo(string ApplyPeople, int Total, int Finished, int Speed)
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = "server=.;database=patents;uid=sa;pwd=mingrui";
            SqlCommand mysql = con.CreateCommand();
            mysql.CommandText = "insert into PLANMAP(ApplyPEO,PatentSUM,Finished,Speed) values('"
                + ApplyPeople + "','"
                + Total + "','"
                + Finished + "','"
                + Speed
                + "')";
            try
            {
                con.Open();
                mysql.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                SaveLogInformation(ex.Message);
            }
            finally
            {
                con.Close();
            }
        }
        #endregion
        //#region 获得申请人名称及相关查询信息
        //static public string SelectApplyPeopleInfo()
        //{
        //    string info = null;
        //    SqlConnection con = new SqlConnection();
        //    con.ConnectionString = "server=.;database=patents;uid=sa;pwd=mingrui";
        //    SqlCommand mysqlpeo = con.CreateCommand();
        //    mysqlpeo.CommandText = "select ApplyPEO from PLANMAP";
        //    SqlCommand mysqlsum = con.CreateCommand();
        //    mysqlsum.CommandText = "select PatentSUM from PLANMAP";
        //    SqlCommand mysqlfininshed = con.CreateCommand();
        //    mysqlfininshed.CommandText = "select Finished from PLANMAP";
        //    SqlCommand mysqlspeed = con.CreateCommand();
        //    mysqlspeed.CommandText = "select Speed from PLANMAP";
        //    try
        //    {
        //        con.Open();
        //    }
        //    catch (Exception ex)
        //    {
        //        SaveLogInformation(ex.Message);
        //    }
        //    try
        //    {
        //        SqlDataReader readpeo = mysqlpeo.ExecuteReader();
        //        SqlDataReader readsum = mysqlsum.ExecuteReader();
        //        SqlDataReader readfinished = mysqlfininshed.ExecuteReader();
        //        SqlDataReader readspeed = mysqlspeed.ExecuteReader();
        //        while (readpeo.Read() && readsum.Read() && readfinished.Read() && readspeed.Read())
        //        {
        //            info += readpeo[0]+"\n"
        //                +readsum
        //        }
        //    }
        //    catch(Exception ex)
        //    {
        //        log.errorlog(ex.Message);
        //    }
        //    con.Close();
        //    return info;
        //}
        //#endregion
    }
}
